# -*- encoding: utf-8 -*-
# @File   : handle_excel.py
# @Time   : 2020/9/19 15:27
# @Author : Dapan
# @Email  : wali666@qq.com


# # ====================================================  一、使用 csv  ==============================================
import csv
import os


def initCSV(fileName):
    """
    初始化csv文件
    :param fileName:
    :return:
    """
    title1 = ['发布时间', '岗位名称', '企业名称', '区域', '薪资', '学历', '工作经验', '招聘人数', '详情页url']
    # 文件路径
    dir = './dataCSV'
    if not os.path.exists(dir):
        os.makedirs(dir)
    filePath = '%s/%s.csv'%(dir, fileName)
    file1 = open(filePath, 'w', newline='', encoding='utf8')  # 打开文件
    content1 = csv.writer(file1, dialect='excel')  # 设定文件写入模式
    content1.writerow(title1)
    file1.close()
    return filePath

def writeCSV(rowData, path):
    """保存数据"""
    file1 = open(path, 'a', newline='')  # 打开文件
    content1 = csv.writer(file1, dialect='excel')  # 设定文件写入模式
    content1.writerow(rowData)
    file1.close()

def readCSV(filePath):
    """
    读取数据
    :param filePath: csv文件路径
    :return:
    """
    with open(filePath, "r", encoding='utf8') as csvfile:
        reader = csv.reader(csvfile)
        # 行数据
        rowList = [row for row in reader]
        print(rowList)
    return rowList



# ====================================================  二、xlrd 与 xlwt  ============================================
import xlrd
import xlwt
import json
from xlutils.copy import copy


def init_xls(topData,FILE_PATH):
    """
    文件内写入标题
    :param row_data: 标题列表
    :return: 文件位置
    """
    save_file_path = FILE_PATH
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet0 = workbook.add_sheet('排名数据')
    worksheet0.write(0, 0, label='标题')
    worksheet0.write(0, 1, label='排名')
    worksheet0.write(0, 2, label='综合评分')
    worksheet0.write(0, 3, label='链接')
    for i in topData:
        # 创建一个worksheet
        worksheet = workbook.add_sheet('top-%s'%(topData.index(i)+1))
        # 写入excel
        # 参数对应 行, 列, 值
        title_list = ['评论账户名','评论内容','评论回复内容']
        for title in title_list:
            worksheet.write(0, title_list.index(title), label=title)

    # 保存
    workbook.save(save_file_path)
    print('xls初始化完成')
    return save_file_path


# 在原文件内追加内容 使用xlrd
def write_content(f_path,contentPageData):
    """
    文件内写入一页内容
    :param f_path: 文件位置
    :param pageData: 每页数据
    :return: 无
    """
    sheetName = '评论数据'
    # 打开文件
    rb = xlrd.open_workbook(f_path,encoding_override='utf-8')
    row = contentPageData['rankingContent']  # 行号
    # 复制内容
    wb = copy(rb)
    # 从复制的文件中得到第一个sheet
    sheet = wb.get_sheet(sheetName)
    sheet.write(row,0,row)
    sheet.write(row,1,contentPageData['uname'])
    sheet.write(row,2,contentPageData['contentText'])
    sheet.write(row,3,json.dumps(contentPageData['contentRepliesText'], ensure_ascii=False))
    wb.save(f_path)


# 使用xlwt 写入
def write_xls():
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建一个worksheet
    worksheet = workbook.add_sheet('My Worksheet')
    # 写入excel
    # 参数对应 行, 列, 值
    worksheet.write(1, 0, label='this is test')
    # 保存
    workbook.save('Excel_test.xls')



# ====================================================  三、openpyxl  ============================================
# 不支持csv xls
import openpyxl


def initXlsx(xlsxPath):
    book = openpyxl.Workbook()  # 新建工作簿
    sheet = book.active
    title_list2 = ['日期',
                   '监测点位',
                   '二氧化硫',
                   '二氧化氮',
                   '可吸入颗粒物',
                   '一氧化碳',
                   '臭氧',
                   '细颗粒物',
                   '空气质量指数',
                   '首要污染物',
                   'AQI指数级别',
                   'AQI指数类别']
    for i in range(len(title_list2)):
        sheet.cell(1, i + 1, title_list2[i])  # 行，列，值 这里是从1开始计数的
    book.save(xlsxPath)
    book.close()

def readExcel(filePath):
    # 获取到要打开的excel
    wb = openpyxl.load_workbook(filePath)
    # 获取要获取的工作簿
    # sheet = wb['Sheet']
    sheet = wb.active
    # 读取excel里面的数据
    # header_data = []  # 读取所有标题
    #             #  行   列
    # for i in range(1, sheet.max_column + 1):
    #     headers = sheet.cell(row=1, column=i).value
    #     header_data.append(headers)  # 读取测试数据
    # print(header_data)

    rowsData = []  # 所有表数据
    for i in range(1, sheet.max_row + 1):
        rowData = []
        for j in range(1, sheet.max_column + 1):
            rowData.append(sheet.cell(row=i, column=j).value)
        rowsData.append(rowData)
    print(rowsData)

    return rowsData

def xlsxSave(xlsxPath, rowDataList):
    data = openpyxl.load_workbook(xlsxPath)
    table = data['Sheet1']
    max_row = table.max_row  # 获得行数
    for rowData in rowDataList:
        for index in range(len(rowData)):
            table.cell(max_row+1, index+1).value = rowData[index]
        max_row+=1

    data.save(xlsxPath)
    data.close()


if __name__ == '__main__':
    filePath = './data.xlsx'
    readExcel(filePath)